package ex55.mm.flow.impound.quote.common.rdsdb;

import com.alibaba.druid.pool.DruidDataSource;
import ex55.mm.flow.impound.quote.common.kit.StringFormat;
import ex55.mm.flow.impound.quote.common.kit.URLParser;
import org.apache.commons.dbutils.DbUtils;
import org.jooq.*;
import org.jooq.conf.ParamType;
import org.jooq.impl.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator;
import org.springframework.boot.autoconfigure.jooq.SpringTransactionProvider;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
import org.springframework.lang.Nullable;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

/**
 *
 */
public class RdsDBHelper {
    static {
        System.getProperties().setProperty("org.jooq.no-logo", "true");
    }

    @Nullable
    public static String rString(Map<String, Object> rowMap, String name) {
        if (rowMap == null) {
            return null;
        }
        Object o = rowMap.get(name);
        if (o == null) {
            return null;
        }
        return o.toString();
    }

    public static BigDecimal rBigDecimal(Map<String, Object> rowMap, String name) {
        if (rowMap == null) {
            return null;
        }
        Object o = rowMap.get(name);
        if (o == null) {
            return null;
        }
        return new BigDecimal(o.toString());
    }

    public DataSource getDataSourceMaster() {
        return dataSourceMaster;
    }

    public DataSource getDataSourceReader() {
        return dataSourceReader;
    }

    private static DruidDataSource DruidDataSource(String dataSource_url
            , String dataSource_username
            , String dataSource_password
            , boolean readOnly) {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(dataSource_url);
        dataSource.setUsername(dataSource_username);//用户名
        dataSource.setPassword(dataSource_password);//密码
        dataSource.setInitialSize(2);
        dataSource.setMaxActive(200);
        dataSource.setMinIdle(0);
        dataSource.setMaxWait(TimeUnit.SECONDS.toMillis(60));
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setTestOnBorrow(false);
        dataSource.setTestWhileIdle(true);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setDefaultReadOnly(readOnly);
        return dataSource;
    }


    public static RdsDBHelper createRdsHelperMysqlUrl(String mysqlUrl) {
        URLParser rdsUri = URLParser.create(mysqlUrl);
        Assert.isTrue(rdsUri.getProtocol().equals("mysql"), "mysql uri error");
        String rdsHost = rdsUri.getHost();
        String[] splitRdsHost = rdsHost.split(";", 2);
        Assert.isTrue(splitRdsHost.length == 2, "mysql host uri error");
        String[] rdsUser = rdsUri.getUserInfo();
        logger.debug("rdsHost:{},splitRdsHost:{},rdsUser:{}", rdsHost, splitRdsHost, rdsUser);
        return new RdsDBHelper(
                RdsDBHelper.DruidDataSource(
                        RdsDBHelper.createFullDatasource_url(
                                splitRdsHost[0], rdsUri.getPort(), rdsUri.getPath(0)
                        )
                        , rdsUser[0]
                        , rdsUser[1]
                        , false),
                RdsDBHelper.DruidDataSource(
                        RdsDBHelper.createFullDatasource_url(
                                StringUtils.hasText(splitRdsHost[1]) ? splitRdsHost[1] : splitRdsHost[0], rdsUri.getPort(), rdsUri.getPath(0)
                        )
                        , rdsUser[0]
                        , rdsUser[1]
                        , true)
        );
    }


    private static String createFullDatasource_url(String host, int port, String dateSourceName) {
        return "jdbc:mysql://" + host + ":" + port + "/" + dateSourceName + "?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
    }

    private static DefaultDSLContext createDSLContext(DataSource dataSource, PlatformTransactionManager txManager) {
        DefaultConfiguration configuration = new DefaultConfiguration();
        configuration.set(SQLDialect.MYSQL);
        configuration.setConnectionProvider(new DataSourceConnectionProvider(new TransactionAwareDataSourceProxy(dataSource)));
        configuration.setTransactionProvider(new SpringTransactionProvider(txManager));
        configuration.setExecuteListenerProvider(new DefaultExecuteListenerProvider(new JooqExceptionTranslator()));
        configuration.settings().withRenderSchema(false);
        //
        return new DefaultDSLContext(configuration);
    }


    private final static Logger logger = LoggerFactory.getLogger(RdsDBHelper.class);

    private final DataSource dataSourceMaster;
    private final DataSource dataSourceReader;

    private final DSLContext masterDslContext;
    private final DSLContext readerDslContext;

    private final DataSourceTransactionManager masterTransactionManager;
    private final DataSourceTransactionManager readerTransactionManager;


    private RdsDBHelper(DataSource dataSourceMaster, DataSource dataSourceReader) {
        this.dataSourceMaster = dataSourceMaster;
        this.dataSourceReader = dataSourceReader;
        this.masterTransactionManager = new DataSourceTransactionManager(dataSourceMaster);
        this.readerTransactionManager = new DataSourceTransactionManager(dataSourceReader);
        //
        this.masterDslContext = createDSLContext(dataSourceMaster, masterTransactionManager);
        this.readerDslContext = createDSLContext(dataSourceReader, readerTransactionManager);
        //
    }


    //////===

    public DSLContext masterDslContext() {
        return masterDslContext;
    }

    public DSLContext readerDslContext() {
        return readerDslContext;
    }

    public void doTransaction(TransactionRunnable runnable) {
        TransactionStatus tx = masterTransactionManager.getTransaction(new DefaultTransactionDefinition());
        try {
            runnable.run();
            masterTransactionManager.commit(tx);
        } catch (RuntimeException e) {
            masterTransactionManager.rollback(tx);
            throw e;
        } catch (Exception e) {
            masterTransactionManager.rollback(tx);
            throw new TransactionDatabaseException(e);
        }
    }


    public <T> T doTransactionResult(TransactionCallable<T> runnable) {
        TransactionStatus tx = masterTransactionManager.getTransaction(new DefaultTransactionDefinition());
        try {
            T t = runnable.run();
            masterTransactionManager.commit(tx);
            return t;
        } catch (RuntimeException e) {
            masterTransactionManager.rollback(tx);
            throw e;
        } catch (Exception e) {
            masterTransactionManager.rollback(tx);
            throw new TransactionDatabaseException(e);
        }
    }

    public <Z extends Record> Z selectMostOne(Table<Z> table, Condition c, boolean consistentRead) {
        DSLContext dslContext = consistentReadDSL(consistentRead);
        return dslContext
                .select()
                .from(table)
                .where(c)
                .limit(1)
                .fetchOneInto(table);
    }


    public <Z extends Record> int updateMostOne(Table<Z> table, ValuesBuilder values, Condition condition) {
        return doTransactionResult(() -> {
            UpdateConditionStep<Z> sql = masterDslContext
                    .update(table)
                    .set(values.values())
                    .where(condition);
            int execute = sql.execute();
            if (execute > 1) {
                throw new RuntimeException(
                        StringFormat.string("updateMostOne Error, table:{},values:{},con:{},execute:{}"
                                , table.getName(), values, sql.getSQL(ParamType.INLINED), execute)
                );
            }
            return execute;
        });
    }

    public <Z extends Record> int deleteMostExpect(Table<Z> table, Condition condition, int mostExpect) {
        return doTransactionResult(() -> {
            DeleteConditionStep<Z> sql = masterDslContext
                    .delete(table)
                    .where(condition);
            logger.debug("[deleteMostExpect] sql:{}", sql.getSQL(ParamType.INLINED));
            int execute = sql.execute();
            if (execute > mostExpect) {
                throw new RuntimeException(
                        StringFormat.string("deleteMostExpect Error, table:{},con:{},execute:{},mostExpect:{}"
                                , table.getName(), sql.getSQL(ParamType.INLINED), execute, mostExpect)
                );
            }
            return execute;
        });
    }

    public <Z extends Record> int deleteMostOne(Table<Z> table, Condition condition) {
        return deleteMostExpect(table, condition, 1);
    }

    public <Z extends Record> void insertMustOne(Table<Z> table, ValuesBuilder values, boolean ignoreDuplicate) {
        doTransaction(() -> {
            try {
                InsertSetMoreStep<Z> sql = masterDslContext
                        .insertInto(table)
                        .set(values.values());
                int execute = sql.execute();
                if (execute != 1) {
                    throw new RuntimeException(
                            StringFormat.string("insertMustOne Error, table:{},values:{},con:{},execute:{}"
                                    , table.getName(), values, sql.getSQL(ParamType.INLINED), execute)
                    );
                }
            } catch (DuplicateKeyException e) {
                if (!ignoreDuplicate) {
                    throw e;
                }
            }
        });
    }

    public <Z extends Record> int insertDuplicateUpdateMostOne(Table<Z> table, ValuesBuilder insertValues, ValuesBuilder updateValues) {
        return doTransactionResult(() -> {
            InsertOnDuplicateSetMoreStep<Z> sql = masterDslContext
                    .insertInto(table)
                    .set(insertValues.values())
                    .onDuplicateKeyUpdate()
                    .set(updateValues.values());
            int execute = sql.execute();
            if (execute > 2) {
                throw new RuntimeException(
                        StringFormat.string("onDuplicateKeyUpdateMostOne Error, table:{},insertValues:{},updateValues:{},con:{},execute:{}"
                                , table.getName(), insertValues, updateValues, sql.getSQL(ParamType.INLINED), execute)
                );
            }
            return execute;
        });
    }

    public DSLContext consistentReadDSL(boolean consistentRead) {
        return consistentRead ? masterDslContext : readerDslContext;
    }


    public interface LazyFetchRecord {
        void onRecords(Cursor<Record> records) throws Exception;
    }

    public void lazyFetch(String sql, List<Object> bindParams, boolean consistentRead, LazyFetchRecord lazyFetchRecord) {
        try {
            DataSource dataSource = consistentRead ? dataSourceMaster : dataSourceReader;
            try (Connection connection = dataSource.getConnection()) {
                PreparedStatement ps = connection.prepareStatement(sql,
                        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                for (int i = 0; i < bindParams.size(); i++) {
                    Object param = bindParams.get(i);
                    StatementCreatorUtils.setParameterValue(
                            ps, i + 1
                            , StatementCreatorUtils.javaTypeToSqlParameterType(param.getClass())
                            , param
                    );
                }
                ps.setQueryTimeout(0);
                ps.setFetchSize(Integer.MIN_VALUE);
                ResultSet rs = ps.executeQuery();
                try (Cursor<Record> records = DSL.using(SQLDialect.MYSQL).fetchLazy(rs)) {
                    lazyFetchRecord.onRecords(records);
                }
                try {
                    DbUtils.closeQuietly(rs);
                } finally {
                    DbUtils.closeQuietly(ps);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void close() {
        readerDslContext.close();
        masterDslContext.close();
    }
}
